package com.wolfking.converter.transform.extend;

import com.google.common.collect.Lists;
import com.google.gson.*;
import com.wolfking.converter.transform.AbstractExtendTransform;
import com.wolfking.converter.util.CaesarUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;

import java.nio.charset.StandardCharsets;
import java.util.Collection;
import java.util.List;
import java.util.Objects;
import java.util.Optional;
import java.util.stream.Stream;

@Slf4j
public class JsonExtract4htmlTransform extends AbstractExtendTransform<Object> {


    private String path;

    private final String format = "<textarea disabled rows='10' style= 'height: 400px; width: 1200px;'>%s</textarea>";

    private boolean textAreaShow = false;

    private static final Gson gson = new GsonBuilder().setPrettyPrinting().create();


    public JsonExtract4htmlTransform() {
        this("");
    }

    public JsonExtract4htmlTransform(String path) {
        try {
            JsonElement element = JsonParser.parseString(path);
            if (element.isJsonPrimitive()) {
                this.path = element.getAsJsonPrimitive().getAsString();
            } else {
                this.path = element.getAsJsonObject().get("path").getAsString();
                this.textAreaShow = element.getAsJsonObject().get("textAreaShow").getAsBoolean();
            }
        } catch (Exception e) {
            this.path = path;
        }
    }

    @Override
    public String transform(Object value) {
        if (Objects.isNull(value)) {
            return "";
        }
        String beforeValue;
        if (value instanceof String) {
            beforeValue = value.toString();
        } else if (value instanceof byte[]) {
            beforeValue = new String((byte[]) value, StandardCharsets.UTF_8);
        } else {
            beforeValue = value.toString();
        }

        if (CaesarUtil.isEncrypted(beforeValue)) {
            beforeValue = CaesarUtil.decode(beforeValue);
        }

        try {
            JsonElement element = JsonParser.parseString(beforeValue);
            if (!element.isJsonArray() && !element.isJsonObject()) {
                return beforeValue;
            }
            List<String> maskString = extractString(element, path);
            if (CollectionUtils.isNotEmpty(maskString) && maskString.size() == 1 && textAreaShow) {
                return String.format(format, maskString.get(0).replace("\\r\\n\\t", "\n\t")
                        .replace("\\r\\n", "\n")
                        .replace("\\r\\t", "\t")
                        .replace("\\r", "")
                        .replace("\\t", "\t")
                        .replace("\\n", "\n"));
            }
            return String.join(",", maskString).replace("\\r\\n\\t", "\n\t")
                    .replace("\\r\\n", "\n")
                    .replace("\\r\\t", "\t")
                    .replace("\\r", "")
                    .replace("\\t", "\t")
                    .replace("\\n", "\n")
                    .replace("\n", "<BR>")
                    .replace("\t", "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;")
                    .replace("\t\t", "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;")
                    .replace(" ", "&nbsp;&nbsp;")
                    ;
        } catch (Exception e) {
            log.warn("", e);
            return beforeValue;
        }
    }

    public static List<String> extractString(JsonElement element, String path) {
        List<String> overall = Lists.newArrayList();
        try {

            if (element.isJsonObject()) {
                JsonObject object = element.getAsJsonObject();
                extractElement(object, path, overall);
            } else if (element.isJsonArray()) {
                JsonArray array = element.getAsJsonArray();
                for (JsonElement ele : array) {
                    extractElement(ele.getAsJsonObject(), path, overall);
                }
            }
        } catch (Throwable e) {
            log.error("mask error", e);
        }
        return overall;
    }

    private static void extractElement(JsonElement element, String path, Collection<String> overall) {
        if (element != null) {
            String[] paths = path.split("\\.");
            if (paths.length == 1) {
                if (element.isJsonObject()) {
                    JsonObject asJsonObject = element.getAsJsonObject();
                    JsonElement asJsonElement = asJsonObject.get(path);
                    if (asJsonElement == null || asJsonElement.isJsonNull()) {
                        return;
                    }
                    if (asJsonElement.isJsonPrimitive()) {
                        JsonPrimitive primitive = asJsonObject.getAsJsonPrimitive(path);
                        if (primitive != null) {
                            String asString = primitive.getAsString();
                            if (StringUtils.isNotBlank(asString)) {
                                overall.add(asString);
                            }
                        }
                    } else {
                        overall.add(gson.toJson(asJsonElement));
                    }
                } else if (element.isJsonArray()) {
                    JsonArray array = element.getAsJsonArray();
                    for (JsonElement ele : array) {
                        extractElement(ele.getAsJsonObject(), path, overall);
                    }
                }
            } else {
                Optional<String> reduce = Stream.of(paths).skip(1).reduce((e1, e2) -> (e1 + "." + e2));
                if (reduce.isPresent()) {
                    if (element.isJsonObject()) {
                        JsonObject asJsonObject = element.getAsJsonObject();
                        extractElement(asJsonObject.get(paths[0]), reduce.get(), overall);
                    } else if (element.isJsonArray()) {
                        JsonArray asJsonArray = element.getAsJsonArray();
                        for (JsonElement jsonElement : asJsonArray) {
                            extractElement(jsonElement.getAsJsonObject().get(paths[0]), reduce.get(), overall);
                        }
                    }
                }
            }
        }
    }

    public static void main(String[] args) throws Exception {
        String sql = "{\n" +
                "  \"autoWebLoad\": \"false\",\n" +
                "  \"hideColumns\": \"\",\n" +
                "  \"htmlCenterShow\": \"true\",\n" +
                "  \"underLineToCamel\": \"true\",\n" +
                "  \"sqlDescribe\": \"代理还款查询\",\n" +
                "  \"id\": \"2\",\n" +
                "  \"paramVerify\": \"<#assign verifyMessage= ''/>\\r\\n<#assign notIndexFields= ['partner_user_id','proxy_serial_no','pay_serial_no','partner_pay_no','partner_loan_no','proxy_loan_no','account_loan_key','fund_loan_key']/>\\r\\n<#assign containsField = false/>\\r\\n<#list notIndexFields as paramName>\\r\\n    <#if params?keys?seq_contains(paramName)>\\r\\n\\t\\t<#assign containsField = true/>\\r\\n    </#if>\\r\\n</#list>\\r\\n<#if !containsField>\\r\\n\\t<#if !params?keys?seq_contains('create_time_start') || !params?keys?seq_contains('create_time_end') >\\r\\n\\t\\t请输入其他信息或者时间范围\\r\\n\\t<#elseif fm.dateDifference(params.create_time_start,params.create_time_end) gt 300000>\\r\\n\\t\\t没有任何索引字段，数据量比较大查询时间不能超过5分钟\\r\\n\\t</#if>\\r\\n</#if>\",\n" +
                "  \"dataSource\": \"proxy\",\n" +
                "  \"sqlBase64\": \"select \\r\\nu.`user_name` as `用户名`,\\r\\nup.amount as `金额`,\\r\\nIFNULL(up.coupon_derate_amount,0) as `减免`,\\r\\nIFNULL(up.frozen_amount,0) as `冻`, \\r\\n\\r\\n-- u.id as `代理用户ID`,\\r\\nIF(up.pay_target_type = 'BILL','账单','订单') as `类型`,\\r\\nCONCAT(IF(up.pay_target_type = 'BILL',(select GROUP_CONCAT(ubus.origin_funder) from user_bill ub INNER JOIN user_loan ubul on ub.id = ubul.user_bill_id INNER JOIN user_loan_subject_info ubus on ubul.id = ubus.user_loan_id where ub.proxy_serial_no = up.proxy_loan_no and ubul.account_report_status = 2),CONCAT(us.origin_funder))) as `资方`,\\r\\nIFNULL(up.pay_channel,'无') as `渠道`,\\r\\nIFNULL(up.pay_subject,'无') as `主体`,\\r\\nIF(up.account_check_status = -1 OR up.payment_confirm_status=-1 OR (up.payment_confirm_status=8 AND up.pay_charge_status =2 )OR up.payment_sms_check_status = -1 OR (up.account_check_status = 2 and up.payment_confirm_status=2 and up.account_report_status = 2), '终态','非终态') as `状态`,\\r\\ncase WHEN up.pay_launch_type = 0 then '自动还款' WHEN up.pay_launch_type = 1 then '手动还款' WHEN up.pay_launch_type = 2 then '催收还款' WHEN up.pay_launch_type = 3 then '汇款还款' WHEN up.pay_launch_type = 4 then '还款任务' WHEN up.pay_launch_type = 5 then '资金方还款' ELSE '其他' END as `发起方式`,\\r\\nup.pay_type as `还款方式`,\\r\\nup.sms_code as `验证码`,\\r\\n\\r\\ncase WHEN upe.split_type is NULL then '未拆单' WHEN upe.split_type = 0 then '普通订单' WHEN upe.split_type = 1 then '主订单' WHEN upe.split_type = 2 then '子订单' WHEN upe.split_type = 3 then '平行订单' WHEN upe.split_type = 4 then '未知' ELSE '其他' END as `模式`,\\r\\ncase WHEN up.account_check_status in (0,-1) THEN IFNULL(GROUP_CONCAT(IF(j.machine_status = 'payToCheck',j.last_error,NULL)),IF(up.account_check_status = -1,'未通过','待处理')) WHEN up.account_check_status = 1 THEN IF(LENGTH(GROUP_CONCAT(IF(j.machine_status = 'payToCheck',j.last_error,NULL))) > 0,GROUP_CONCAT(IF(j.machine_status = 'payToCheck',j.last_error,NULL)),'检查未通过') WHEN up.account_check_status = 2 THEN '成功' ELSE '未知' END as `检查`,\\r\\nDATE_FORMAT(up.create_time,'%y-%m-%d %H:%i') as `创建时间`,\\r\\nIFNULL(DATE_FORMAT(up.account_check_time,'%y-%m-%d %H:%i'),'') as `检查时间`,\\r\\nCONCAT(TIMESTAMPDIFF(MINUTE,IFNULL(min(IF(j.machine_status = 'payToCheck',j.create_time,NULL)),up.create_time),IFNULL(min(IF(j.machine_status = 'payToCheck',j.update_time,NULL)),up.account_check_time)),'分钟') as `检查耗时`,\\r\\nIF(LENGTH(up.pay_message) > 0,up.pay_message,IF( up.account_check_status in (0,-1),'检查未通过,不走支付',case WHEN up.payment_confirm_status = -1 THEN IF ( LENGTH( GROUP_CONCAT( IF ( j.machine_status = 'payToQuery', j.last_error, NULL ))) > 0, GROUP_CONCAT( IF ( j.machine_status = 'payToQuery', j.last_error, NULL )), IFNULL( up.pay_channel_message, '失败' )) WHEN up.payment_confirm_status = 0 THEN '待处理' WHEN up.payment_confirm_status = 1 THEN '处理中' WHEN up.payment_confirm_status = 2 THEN '成功' ELSE '未知' END)) as `支付`,\\r\\nIFNULL(DATE_FORMAT(up.payment_confirm_time,'%y-%m-%d %H:%i'),'') as `支付时间`,\\r\\nCONCAT(TIMESTAMPDIFF(MINUTE,up.account_check_time,IFNULL(up.payment_confirm_time,min(IF(j.machine_status = 'payToQuery',j.update_time,NULL)))),'分钟') as `支付耗时`,\\r\\ncase WHEN up.account_report_status = -1 THEN IFNULL(GROUP_CONCAT(IF(j.machine_status = 'payToReport',j.last_error,NULL)),'未通过') WHEN up.account_report_status = 0 THEN '待处理' WHEN up.account_report_status = 1 THEN '处理中' WHEN up.account_report_status = 2 THEN '成功' ELSE '未知' END as `上报`,\\r\\nIFNULL(DATE_FORMAT(up.account_report_time,'%y-%m-%d %H:%i'),'') as `上报时间`,\\r\\ncase WHEN up.account_unfrozen_status = -1 THEN '处理失败' WHEN up.account_unfrozen_status = 0 THEN '待处理' WHEN up.account_unfrozen_status = 1 THEN '处理中' WHEN up.account_unfrozen_status = 2 THEN '成功' ELSE '未知' END as `解冻`,\\r\\nIFNULL(DATE_FORMAT(up.partner_call_time,'%y-%m-%d %H:%i'),'') as `回调时间`,\\r\\ncase WHEN up.partner_call_status = -1 THEN '处理失败' WHEN up.partner_call_status = 0 THEN '待处理' WHEN up.partner_call_status = 1 THEN '处理中' WHEN up.partner_call_status = 2 THEN '成功' ELSE '未知' END as `回调`,\\r\\ncase when up.before_pay = 0 then '普通或逾期' WHEN up.before_pay =1 THEN '提前结清' WHEN up.before_pay =2 THEN '退款' ELSE '未知' END as `还款类型`,\\r\\n-- IF(TIMESTAMPDIFF(MINUTE, up.create_time, IFNULL(max(j.update_time),up.update_time)) > 60,CONCAT(FLOOR(TIMESTAMPDIFF(MINUTE, up.create_time, IFNULL(max(j.update_time),up.update_time))/60),'小时',CEIL(mod(TIMESTAMPDIFF(MINUTE, up.create_time, IFNULL(max(j.update_time),up.update_time)),60)),'分'),CONCAT(CEIL(TIMESTAMPDIFF(MINUTE, up.create_time, IFNULL(max(j.update_time),up.update_time))),'分钟')) as `持续时间`,\\r\\nTIMESTAMPDIFF(second, up.create_time, IFNULL(max(j.update_time),up.update_time)) as `持续时间`,\\r\\n\\r\\nup.proxy_serial_no as `序列`,\\r\\n\\r\\nGROUP_CONCAT(distinct up.pay_serial_no) as `支付序列号`,\\r\\nGROUP_CONCAT(j.machine_status,'(状态',j.job_status,'次数',j.execute_times,')' ORDER BY j.id ASC) as `job状态` ,\\r\\nu.partner_user_id as `业务用户ID`,\\r\\nCONCAT(IF(up.pay_target_type = 'BILL',(select GROUP_CONCAT(ubul.proxy_serial_no) from user_bill ub INNER JOIN user_loan ubul on ub.id = ubul.user_bill_id INNER JOIN user_loan_subject_info ubus on ubul.id = ubus.user_loan_id where ub.proxy_serial_no = up.proxy_loan_no and ubul.account_report_status = 2),ul.proxy_serial_no)) as `代理借款key`,\\r\\nCONCAT(IF(up.pay_target_type = 'BILL',(select GROUP_CONCAT(ubul.account_serial_no) from user_bill ub INNER JOIN user_loan ubul on ub.id = ubul.user_bill_id INNER JOIN user_loan_subject_info ubus on ubul.id = ubus.user_loan_id where ub.proxy_serial_no = up.proxy_loan_no and ubul.account_report_status = 2),ul.account_serial_no)) as `账务借款key`,\\r\\nCONCAT(IF(up.pay_target_type = 'BILL',(select GROUP_CONCAT(ubul.proxy_serial_no) from user_bill ub INNER JOIN user_loan ubul on ub.id = ubul.user_bill_id INNER JOIN user_loan_subject_info ubus on ubul.id = ubus.user_loan_id where ub.proxy_serial_no = up.proxy_loan_no and ubul.account_report_status = 2),ul.proxy_serial_no)) as `资金loanKey`,\\r\\nCONCAT(IF(up.pay_target_type = 'BILL',(select GROUP_CONCAT(ubul.partner_loan_no) from user_bill ub INNER JOIN user_loan ubul on ub.id = ubul.user_bill_id INNER JOIN user_loan_subject_info ubus on ubul.id = ubus.user_loan_id where ub.proxy_serial_no = up.proxy_loan_no and ubul.account_report_status = 2),ul.partner_loan_no)) as `业务借款`,\\r\\n\\r\\nu.account_user_id as `账务用户key`,\\r\\n-- 业务锁\\r\\nIFNULL(SUBSTR( GROUP_CONCAT(IF(j.machine_status = 'payToCheck',j.last_error,NULL)), LOCATE( 'lockInfo:{\\\"businessKey\\\":\\\"', GROUP_CONCAT(IF(j.machine_status = 'payToCheck',j.last_error,NULL)) ) + LENGTH( 'lockInfo:{\\\"businessKey\\\":\\\"' ), LOCATE('\\\",\\\"lockType\\\"', GROUP_CONCAT(IF(j.machine_status = 'payToCheck',j.last_error,NULL))) - LOCATE( 'lockInfo:{\\\"businessKey\\\":\\\"', GROUP_CONCAT(IF(j.machine_status = 'payToCheck',j.last_error,NULL)) ) - LENGTH( 'lockInfo:{\\\"businessKey\\\":\\\"' )),'') as `锁`,\\r\\n\\r\\nIF(up.can_pay_off = 1 ,'是','否') as `结清`,\\r\\ncase when upe.split_type = 1 THEN  CONCAT(up.id,',',IFNULL((select GROUP_CONCAT(upe_child.user_pay_id)from user_pay_extend_info upe_child WHERE up.id = upe_child.parent_id ORDER BY upe_child.id ASC),''))\\r\\nwhen upe.split_type = 2 THEN CONCAT(upe.parent_id,',',IFNULL((select GROUP_CONCAT(cc.user_pay_id) from user_pay_extend_info cc where cc.parent_id = upe.parent_id ),'')) ELSE '' END as `父子单ID`,\\r\\nDATE_FORMAT(IFNULL(min(j.job_start_time),min(j.update_time)),'%y-%m-%d %H:%i') as `第一次job成功`,\\r\\nDATE_FORMAT(max(j.update_time),'%y-%m-%d %H:%i') as `job最后时间`,\\r\\nIFNULL(upe.parent_id,-1) as `父单ID`,\\r\\n-- 查到的数据都是有限的，再使用一次查询拼接信息\\r\\nIFNULL((select GROUP_CONCAT(upe_child.user_pay_id)from user_pay_extend_info upe_child WHERE up.id = upe_child.parent_id ORDER BY upe_child.id ASC),'') as `子单ID`,\\r\\nIFNULL((select count(0) from user_pay_extend_info upe_child WHERE up.id = upe_child.parent_id),0) as  `子单数量`,\\r\\n-- 业务锁对应的还款序列号\\r\\nIFNULL(IFNULL((select eup.proxy_serial_no from user_pay eup where eup.partner_id = 5 and eup.partner_pay_no = SUBSTR( GROUP_CONCAT(IF(j.machine_status = 'payToCheck',j.last_error,NULL)), LOCATE( 'lockInfo:{\\\"businessKey\\\":\\\"', GROUP_CONCAT(IF(j.machine_status = 'payToCheck',j.last_error,NULL)) ) + LENGTH( 'lockInfo:{\\\"businessKey\\\":\\\"' ), LOCATE('\\\",\\\"lockType\\\"', GROUP_CONCAT(IF(j.machine_status = 'payToCheck',j.last_error,NULL))) - LOCATE( 'lockInfo:{\\\"businessKey\\\":\\\"', GROUP_CONCAT(IF(j.machine_status = 'payToCheck',j.last_error,NULL)) ) - LENGTH( 'lockInfo:{\\\"businessKey\\\":\\\"' ))),\\r\\n(select eup.proxy_serial_no from user_pay eup where eup.partner_id = 3 and eup.partner_pay_no = SUBSTR( GROUP_CONCAT(IF(j.machine_status = 'payToCheck',j.last_error,NULL)), LOCATE( 'lockInfo:{\\\"businessKey\\\":\\\"', GROUP_CONCAT(IF(j.machine_status = 'payToCheck',j.last_error,NULL)) ) + LENGTH( 'lockInfo:{\\\"businessKey\\\":\\\"' ), LOCATE('\\\",\\\"lockType\\\"', GROUP_CONCAT(IF(j.machine_status = 'payToCheck',j.last_error,NULL))) - LOCATE( 'lockInfo:{\\\"businessKey\\\":\\\"', GROUP_CONCAT(IF(j.machine_status = 'payToCheck',j.last_error,NULL)) ) - LENGTH( 'lockInfo:{\\\"businessKey\\\":\\\"' )))),'') as `锁序列`,\\r\\n-- 业务锁对应的job\\r\\nIFNULL(IFNULL((select GROUP_CONCAT(jb.id,'#',jb.machine_status,'#',jb.job_status) from user_pay eup LEFT JOIN job jb on jb.proxy_serial_no = eup.proxy_serial_no where eup.partner_id  = 5 and eup.partner_pay_no = SUBSTR( GROUP_CONCAT(IF(j.machine_status = 'payToCheck',j.last_error,NULL)), LOCATE( 'lockInfo:{\\\"businessKey\\\":\\\"', GROUP_CONCAT(IF(j.machine_status = 'payToCheck',j.last_error,NULL)) ) + LENGTH( 'lockInfo:{\\\"businessKey\\\":\\\"' ), LOCATE('\\\",\\\"lockType\\\"', GROUP_CONCAT(IF(j.machine_status = 'payToCheck',j.last_error,NULL))) - LOCATE( 'lockInfo:{\\\"businessKey\\\":\\\"', GROUP_CONCAT(IF(j.machine_status = 'payToCheck',j.last_error,NULL)) ) - LENGTH( 'lockInfo:{\\\"businessKey\\\":\\\"' )) GROUP BY eup.proxy_serial_no),\\r\\n(select GROUP_CONCAT(jb.id,'#',jb.machine_status,'#',jb.job_status) from user_pay eup LEFT JOIN job jb on jb.proxy_serial_no = eup.proxy_serial_no where eup.partner_id  = 3 and eup.partner_pay_no = SUBSTR( GROUP_CONCAT(IF(j.machine_status = 'payToCheck',j.last_error,NULL)), LOCATE( 'lockInfo:{\\\"businessKey\\\":\\\"', GROUP_CONCAT(IF(j.machine_status = 'payToCheck',j.last_error,NULL)) ) + LENGTH( 'lockInfo:{\\\"businessKey\\\":\\\"' ), LOCATE('\\\",\\\"lockType\\\"', GROUP_CONCAT(IF(j.machine_status = 'payToCheck',j.last_error,NULL))) - LOCATE( 'lockInfo:{\\\"businessKey\\\":\\\"', GROUP_CONCAT(IF(j.machine_status = 'payToCheck',j.last_error,NULL)) ) - LENGTH( 'lockInfo:{\\\"businessKey\\\":\\\"' )) GROUP BY eup.proxy_serial_no)),'') as `锁job`,\\r\\nGROUP_CONCAT(j.id ORDER BY j.id ASC) as `jobID`,\\r\\n\\r\\nGROUP_CONCAT(distinct up.partner_pay_no) as `业务序列号`\\r\\nfrom user_pay up\\r\\n-- 用户和业务方必有，用内连接\\r\\nINNER JOIN `user` u on  u.id = up.user_id \\r\\nINNER JOIN partner p on p.id = u.partner_id\\r\\n-- job和扩展信息可能没有需要使用左连接\\r\\nLEFT JOIN job j on j.proxy_serial_no = up.proxy_serial_no\\r\\nLEFT JOIN user_pay_extend_info upe on up.id = upe.user_pay_id\\r\\nLEFT JOIN user_loan ul on ul.proxy_serial_no = up.proxy_loan_no\\r\\nLEFT JOIN user_loan_subject_info us on us.user_loan_id =ul.id\\r\\nwhere u.partner_id in (1,2,3,4,5,6,7,8) and up.partner_id in (1,2,3,4,5,6,7,8) \\r\\n-- 业务用户ID\\r\\n  {{partner_user_id}}\\r\\n-- 用户姓名，替换 《罗永发》 汉字\\r\\n   {{real_name}}\\r\\n-- and up.partner_pay_no = '1e2ebf1b644d800a22e1961abe20232d'\\r\\n   {{partner_pay_no}}\\r\\n-- 代理序列号\\r\\n   {{proxy_serial_no}}\\r\\n-- user_pay 的 ID\\r\\n  -- and up.id in  (945609207,945609522,945609524)\\r\\n-- 业务方的借款号\\r\\n {{partner_loan_no}}\\r\\n-- 支付确认的状态，2是成功,0是初始，1是处理中，-1是失败\\r\\n   -- and up.payment_confirm_status = 2\\r\\n   {{payment_confirm_status}}\\r\\n-- 还款的金额\\r\\n --  and up.amount = 0.41\\r\\n   {{amount}}\\r\\n-- 账务上报状态\\r\\n --  and up.account_report_status = 2\\r\\n   {{account_report_status}}\\r\\n   {{account_check_status}}\\r\\n-- and up.pay_subject = 'CY_CF'\\r\\n  {{pay_subject}}\\r\\n  {{pay_channel}}\\r\\n-- 今天的还款\\r\\n  -- and up.create_time > DATE(NOW()) \\r\\n-- 最近几天的还款，此条件不要轻易删除，最好带上时间\\r\\n-- and up.pay_serial_no in ('HHs20107ceb1102b081','HHs20106011c920c03a')\\r\\n  {{pay_serial_no}}\\r\\n-- and up.update_time > SUBDATE(NOW(),INTERVAL 100 DAY)\\r\\n-- and us.origin_funder = 'AJ_TRUST' and up.pay_target_type = 'LOAN' \\r\\n {{funder}}\\r\\n {{proxy_loan_no}}\\r\\n {{create_time_start}}\\r\\n {{create_time_end}}\\r\\n{{fund_loan_key}}\\r\\n{{account_loan_key}}\\r\\n-- job的proxy_serial_no分组，查询结果中用job的属性必须使用聚合函数\\r\\nGROUP BY up.proxy_serial_no \\r\\n\\r\\nHAVING 1=1 \\r\\n{{finish_status}}\\r\\n-- 根据时间倒序排列\\r\\nORDER BY up.create_time DESC\\r\\nlimit :pageNum,:pageSize\",\n" +
                "  \"countSqlBase64\": \"select count(1) from (\\r\\n\\tselect \\r\\n        1,\\r\\n        IF(up.account_check_status = -1 OR up.payment_confirm_status=-1 OR (up.payment_confirm_status=8 AND up.pay_charge_status =2 )OR up.payment_sms_check_status = -1 OR (up.account_check_status = 2 and up.payment_confirm_status=2 and up.account_report_status = 2), '终态','非终态') as `状态`\\r\\n\\tfrom user_pay up\\r\\n\\t-- 用户和业务方必有，用内连接\\r\\n\\tINNER JOIN `user` u on  u.id = up.user_id \\r\\n\\tINNER JOIN partner p on p.id = u.partner_id\\r\\n\\t-- job和扩展信息可能没有需要使用左连接\\r\\n\\tLEFT JOIN job j on j.proxy_serial_no = up.proxy_serial_no\\r\\n\\tLEFT JOIN user_pay_extend_info upe on up.id = upe.user_pay_id\\r\\n\\tLEFT JOIN user_loan ul on ul.proxy_serial_no = up.proxy_loan_no\\r\\n\\tLEFT JOIN user_loan_subject_info us on us.user_loan_id =ul.id\\r\\n\\twhere u.partner_id in (1,2,3,4,5,6,7,8) and up.partner_id in (1,2,3,4,5,6,7,8) \\r\\n\\t-- 业务用户ID\\r\\n\\t{{partner_user_id}}\\r\\n\\t-- 用户姓名，替换 《罗永发》 汉字\\r\\n\\t{{real_name}}\\r\\n\\t-- and up.partner_pay_no = '1e2ebf1b644d800a22e1961abe20232d'\\r\\n\\t{{partner_pay_no}}\\r\\n\\t-- 代理序列号\\r\\n\\t{{proxy_serial_no}}\\r\\n\\t-- user_pay 的 ID\\r\\n\\t-- and up.id in  (945609207,945609522,945609524)\\r\\n\\t-- 业务方的借款号\\r\\n\\t{{partner_loan_no}}\\r\\n\\t-- 支付确认的状态，2是成功,0是初始，1是处理中，-1是失败\\r\\n\\t-- and up.payment_confirm_status = 2\\r\\n\\t{{payment_confirm_status}}\\r\\n\\t-- 还款的金额\\r\\n\\t--  and up.amount = 0.41\\r\\n\\t{{amount}}\\r\\n\\t-- 账务上报状态\\r\\n\\t--  and up.account_report_status = 2\\r\\n\\t{{account_report_status}}\\r\\n       {{account_check_status}}\\r\\n\\t-- and up.pay_subject = 'CY_CF'\\r\\n\\t{{pay_subject}} \\r\\n        {{pay_channel}}\\r\\n\\t-- 今天的还款\\r\\n\\t-- and up.create_time > DATE(NOW()) \\r\\n\\t-- 最近几天的还款，此条件不要轻易删除，最好带上时间\\r\\n\\t-- and up.pay_serial_no in ('HHs20107ceb1102b081','HHs20106011c920c03a')\\r\\n\\t{{pay_serial_no}}\\r\\n\\t-- and us.origin_funder = 'AJ_TRUST' and up.pay_target_type = 'LOAN' \\r\\n\\t{{funder}}\\r\\n\\t{{proxy_loan_no}}\\r\\n        {{create_time_start}}\\r\\n       {{fund_loan_key}}\\r\\n       {{account_loan_key}}\\r\\n\\t\\t{{create_time_end}}\\r\\n\\t-- job的proxy_serial_no分组，查询结果中用job的属性必须使用聚合函数\\r\\n\\tGROUP BY up.proxy_serial_no \\r\\n\\t-- 根据时间倒序排列\\r\\n\\tHAVING 1=1 \\r\\n       {{finish_status}}\\r\\n) t\",\n" +
                "  \"version\": \"62\",\n" +
                "  \"allowEmptyParamQuery\": \"false\"\n" +
                "}\n";
        String constructArg = new JsonExtract4htmlTransform("{\n" +
                "\t\"path\": \"paramVerify\",\n" +
                "\t\"format\": \"<textarea disabled rows='15' style= 'height: 400px; width: 1000px;'>%s</textarea>\"\n" +
                "}").transform(sql);
        System.out.println(constructArg);
    }
}
